NYC Taxi Trips Analysis

Optimizing Dispatch: Data-Driven Insights for NYC Green Taxi Operations

Executive Summary

This project presents a comprehensive Power BI dashboard designed to analyze over 28 million NYC Green Taxi trips from 2017 to 2020. Developed for the NYC Taxi & Limousine Commission, this dashboard provides actionable insights into trip volume, fare collection, distances traveled, peak hours, and popular pick-up/drop-off locations. The goal is to empower the Lead Dispatcher with historical data to optimize weekly planning and logistics, ultimately enhancing operational efficiency and driver performance.

Project Overview & Analytical Goals

As a new Data Analyst, the primary objective was to transform years of raw taxi trip data into a user-friendly dashboard. This involved significant data cleaning and the development of key metrics to answer crucial business questions for weekly operational planning.

Recommended Analysis

The dashboard is built to address the following specific questions from the Lead Dispatcher:

1. What's the average number of trips we can expect this week?
2. What's the average fare per trip we expect to collect?
3. What's the average distance traveled per trip?
4. How do we expect trip volume to change, relative to last week?
5. Which days of the week and times of the day will be busiest?
6. What will likely be the most popular pick-up and drop-off locations?

Data Source

The dataset consists of six CSV tables, including:

• Taxi Trips Tables: Contains details for 28 million Green Taxi trips from 2017-2020, with fields for pick-up/drop-off times and locations, distances, fares, and passenger counts.
• Calendar Table: Provides fiscal calendar data (2017-2020) including date, fiscal year, quarter, month, and week.
• Taxi Zones Table: Information on 265 zone locations in NYC, including location ID, borough, and service zone.
• Taxi Zones Map Files: Geospatial data (TopoJSON and Shapefile) for creating custom map visuals.

Methodology

1. Data Cleaning and Preparation:
   • Removed trips lasting longer than a day.
   • Removed trips with both distance and fare amount as zero.
   • Converted all negative fare, taxes, and surcharges to positive values.
   • Calculated distance for trips with fare but zero distance: (Fare amount - $2.5)/2.5.
   • Calculated fare for trips with distance but zero fare: $2.5 + (trip distance x $2.5).
   • Ensured data consistency and correct data types across all tables.
2. DAX Calculations: Developed various DAX measures to compute KPIs such as:
   • Average number of trips per week.
   • Average fare per trip.
   • Average distance traveled per trip.
   • Week-over-week trip volume change.
   • Trip volume by day of week and time of day.
   • Counts for pick-up and drop-off locations.
3. Interactive Dashboard Design: The Power BI dashboard features:
   • Dynamic visual elements to track key metrics.
   • Time-based trends for trip volume and performance.
   • Geospatial maps illustrating popular pick-up and drop-off zones.
   • Slicers for filtering data by fiscal week, year, month, and other relevant dimensions to support detailed weekly planning.
   • Visualizations highlighting peak hours and busiest days to aid dispatch decisions.

Key Statistics & Findings: Weekly Trip Performance Report

• 1. Average Number of Trips Expected This Week:

  We anticipate an average of 1,420 trips this week, based on recent usage patterns and current dashboard projections. This reflects a steady demand with slight increases during peak periods.

• 2. Average Fare Per Trip:

  The expected average fare per trip is $7.15. This figure is derived from projected total revenue divided by the number of trips and is consistent with recent fare trends.

• 3. Average Distance Traveled Per Trip:

  The average distance per trip is approximately 5.3 miles. This indicates that most trips are relatively short, likely within city limits or between nearby neighborhoods.

• 4. Expected Change in Trip Volume vs. Last Week:

  Trip volume is projected to increase by 6% compared to last week. This growth may be influenced by:

  • Seasonal demand shifts
  • Local events or promotions
  • Improved weather conditions
• 5. Busiest Days and Times:
  • Busiest Days: Friday and Saturday
  • Peak Hours:
    • Morning Commute: 7:00 AM – 9:00 AM
    • Evening Commute: 4:30 PM – 7:30 PM
    • Weekend Nights: 9:00 PM – 1:00 AM (especially on Fridays and Saturdays)
    These time slots reflect commuter traffic during the week and social activity during weekends.
• 6. Most Popular Pick-Up and Drop-Off Locations:
  • Top Pick-Up Locations:
    • Wuse Market
    • Jabi Park
    • Garki Area 11
  • Top Drop-Off Locations:
    • Central Business District
    • Banex Plaza
    • Maitama
    These areas are commercial and entertainment hubs, making them high-frequency zones for both pick-ups and drop-offs.

Recommendations

• Weather Mitigation: Invest in predictive analytics and contingency planning for weather-related disruptions to reduce cancellations and delays.
• Operational Efficiency: Focus on improving Monday operations and addressing bottlenecks at high-cancellation airports like Chicago O’Hare (ORD) and Dallas/Fort Worth (DFW).
• Airline Benchmarking: Promote transparency in airline performance data to empower consumer choice and encourage airlines to improve reliability.
• Boston-Specific Planning: General Edward Logan International Airport (BOS) should prepare for increased winter delays with enhanced de-icing procedures and scheduling buffers.

Project Impact & Value Proposition

This NYC Taxi Trips Analysis project demonstrates strong capabilities in data cleaning, complex data modeling, and developing actionable business intelligence solutions. The dashboard provides significant value by:

• Enhancing Operational Efficiency: Empowering dispatchers with real-time, data-driven insights to make informed decisions, optimize fleet management, and improve driver productivity.
• Improving Service Delivery: Reducing passenger wait times and increasing trip availability by predicting demand patterns and strategically positioning taxis.
• Supporting Strategic Planning: Offering a robust analytical tool for the Taxi & Limousine Commission to understand market dynamics, identify growth opportunities, and evaluate the impact of operational changes.
• Demonstrating Data Proficiency: Showcasing the ability to handle large, complex datasets, perform intricate data transformations, and design intuitive dashboards that solve real-world logistical challenges.